package com.dayuanit.atm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.dayuanit.atm.dao.UserDao;
import com.dayuanit.atm.domain.User;

/**
 * 乐观锁
 * @author Administrator
 *
 */
public class UserDaoImpl implements UserDao {
	
	@Override
	public int addUesr(User user, Connection conn) {
		PreparedStatement pst = null;
		int rows = -1;
		try {
			String sql = "insert into atm_user(card_num, balance, create_time, modify_time) value(?, ?, now(), now());";
			
			pst = conn.prepareStatement(sql);
			pst.setString(1, user.getCardNum());
			pst.setInt(2, user.getBalance());
			
			rows = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (null != pst) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return rows;
	}

	@Override
	public User getUserByCardNum(String cardNum, Connection conn) {
		String sql = "select id, card_num, balance, create_time, modify_time, version from atm_user where card_num=?;";
		
		PreparedStatement pst = null;
		ResultSet rs = null;
		User user = null;
		try {
			pst = conn.prepareStatement(sql);
			
			pst.setString(1, cardNum);
			
			rs = pst.executeQuery();
			while (rs.next()) {
				
				int id = rs.getInt("id");
				String bankCardNum = rs.getNString("card_num");
				int balance = rs.getInt("balance");
				java.sql.Timestamp createTime = rs.getTimestamp("create_time");
				java.sql.Timestamp modifyTime = rs.getTimestamp("modify_time");
				int version = rs.getInt("version");
				
				user = new User();
				user.setBalance(balance);
				user.setCardNum(bankCardNum);
				user.setId(id);
				user.setCreateTime(new Date(createTime.getTime()));
				user.setModifyTime(new Date(modifyTime.getTime()));
				user.setVersion(version);
				
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				
				if (null != pst) {
					pst.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return user;
	}

	@Override
	public int updateBalance(int amount, String cardNum, int oldVersion, Connection conn) {
		PreparedStatement pst = null;
		int rows = -1;
		try {
			String sql = "update atm_user set balance=balance+?, version = version + 1"
					+ " where card_num=? and version=?; ";
			
			pst = conn.prepareStatement(sql);
			
			pst.setInt(1, amount);
			pst.setString(2, cardNum);
			pst.setInt(3, oldVersion);
			
			System.out.println(sql);
			
			rows = pst.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != pst) {
					pst.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return rows;
	}

	@Override
	public int updateBalance(int amount, String cardNum, Connection conn) {
		PreparedStatement pst = null;
		int rows = -1;
		try {
			
			String sql = "update atm_user set balance=balance+? where card_num=?;";
			
			pst = conn.prepareStatement(sql);
			
			pst.setInt(1, amount);
			pst.setString(2, cardNum);
			
			System.out.println(sql);
			rows = pst.executeUpdate();
			
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != pst) {
					pst.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return rows;
	}

	@Override
	public User getUserByCardNumForUpdate(String cardNum, Connection conn) {
		String sql = "select id, card_num, balance, create_time, modify_time, version from atm_user where card_num=? for update;";
		
		PreparedStatement pst = null;
		ResultSet rs = null;
		User user = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, cardNum);
			
			rs = pst.executeQuery();
			while (rs.next()) {
				
				int id = rs.getInt("id");
				String bankCardNum = rs.getNString("card_num");
				int balance = rs.getInt("balance");
				java.sql.Timestamp createTime = rs.getTimestamp("create_time");
				java.sql.Timestamp modifyTime = rs.getTimestamp("modify_time");
				int version = rs.getInt("version");
				
				user = new User();
				user.setBalance(balance);
				user.setCardNum(bankCardNum);
				user.setId(id);
				user.setCreateTime(new Date(createTime.getTime()));
				user.setModifyTime(new Date(modifyTime.getTime()));
				user.setVersion(version);
				
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				
				if (null != pst) {
					pst.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return user;
	}

}
